package br.home.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import br.home.entitys.Garcom;
import br.home.entitys.Mesa;
import br.home.jdbc.ConnectionFactory;
import br.home.utils.UtilsClass;

public class MesaDao {

	
	public static List<Mesa> selectAll(){
		List<Mesa> mesas = null;
		try{
			Connection con = ConnectionFactory.getConnection();
			String sql = "SELECT COUNT(idmesa) as maxrows FROM mesa WHERE finalizada = 0";
			Statement stmt = con.createStatement();
			ResultSet result = stmt.executeQuery(sql);
			result.next();
			if (result.getInt("maxrows") > 0) {
				mesas = new ArrayList<Mesa>();
				sql = "SELECT * FROM mesa WHERE finalizada = 0";
				result = stmt.executeQuery(sql);
				while (result.next()) {
					Mesa mesa = new Mesa();
					mesa.setIdmesa(result.getInt("idmesa"));
					mesa.setNumeromesa(result.getInt("numeromesa"));
					mesa.setDataatendimento(result
							.getDate("dataatendimento"));
					mesa.setHorainicio(result.getTime("horainicio"));
					mesa.setHorafim(result.getTime("horafim"));
					mesa.setValorpago(result.getDouble("valorpago"));
					mesa.setValor10porcento(result
							.getDouble("valor10porcento"));
					mesa.setFinalizada(result.getBoolean("finalizada"));
					mesa.setIdgarcom(result.getInt("idgarcom"));
					Garcom garcom = new Garcom();
					String sql2 = "SELECT * FROM garcom WHERE idgarcom = ? LIMIT 1";
					PreparedStatement stmt2 = con.prepareStatement(sql2);
					stmt2.setInt(1, mesa.getIdgarcom());
					ResultSet result2 = stmt2.executeQuery();
					while (result2.next()) {
						garcom.setId(result2.getInt("idgarcom"));
						garcom.setNome(result2.getString("nome"));
						garcom.setTelefone(result2.getString("telefone"));
						garcom.setAtivo(result2.getBoolean("ativo"));
					}
					mesa.setGarcom(garcom);
					mesas.add(mesa);
					stmt2.close();
				}
				return mesas;
			}else{
				return null;
			}
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}		
	}
	
	public static Mesa selectOne(int idmesa) {
		Mesa mesa;
		try {
			mesa = null;
			Connection con = ConnectionFactory.getConnection();
			String sql = "SELECT * FROM mesa WHERE idmesa = ?";
			PreparedStatement stmt = con.prepareStatement(sql);
			stmt.setInt(1, idmesa);
			ResultSet result = stmt.executeQuery();
			while (result.next()) {
				mesa = new Mesa();
				mesa.setIdmesa(result.getInt("idmesa"));
				mesa.setNumeromesa(result.getInt("numeromesa"));
				mesa.setDataatendimento(result.getDate("dataatendimento"));
				mesa.setHorainicio(result.getTime("horainicio"));
				mesa.setHorafim(result.getTime("horafim"));
				mesa.setValorpago(result.getDouble("valorpago"));
				mesa.setValor10porcento(result.getDouble("valor10porcento"));
				mesa.setFinalizada(result.getBoolean("finalizada"));
				mesa.setIdgarcom(result.getInt("idgarcom"));
			}
			if (mesa != null) {
				return mesa;
			} else {
				return null;
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	public static Boolean checkIfExiste(int nmesa) {
		try {
			Boolean existe = false;
			Connection con = ConnectionFactory.getConnection();
			String sql = "SELECT * FROM mesa WHERE finalizada = 0 AND numeromesa = ?";
			PreparedStatement stmt = con.prepareStatement(sql);
			stmt.setInt(1, nmesa);
			ResultSet result = stmt.executeQuery();
			while (result.next()) {
				existe = true;
			}
			return existe;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	public static Mesa insertMesa(int nmesa, String codigoGarcom) {
		Mesa mesa = null;
		try {
			Connection con = ConnectionFactory.getConnection();
			String sql = "INSERT INTO mesa(numeromesa,dataatendimento,horainicio,idgarcom) VALUES(?,?,?,?)";
			PreparedStatement stmt = con.prepareStatement(sql);
			stmt.setInt(1, nmesa);
			stmt.setString(2, UtilsClass.getData());
			stmt.setString(3, UtilsClass.getTime());
			stmt.setInt(4, Integer.parseInt(codigoGarcom));
			if (!stmt.execute()) {
				sql = "SELECT * FROM mesa WHERE numeromesa = ? AND finalizada=0 ";
				stmt = con.prepareStatement(sql);
				stmt.setInt(1, nmesa);
				ResultSet result = stmt.executeQuery();
				while (result.next()) {
					mesa = new Mesa();
					mesa.setIdmesa(result.getInt("idmesa"));
					mesa.setNumeromesa(result.getInt("numeromesa"));
					mesa.setDataatendimento(result.getDate("dataatendimento"));
					mesa.setHorainicio(result.getTime("horainicio"));
					mesa.setHorafim(result.getTime("horafim"));
					mesa.setValorpago(result.getDouble("valorpago"));
					mesa.setValor10porcento(result.getDouble("valor10porcento"));
					mesa.setFinalizada(result.getBoolean("finalizada"));
					mesa.setIdgarcom(result.getInt("idgarcom"));
					Garcom garcom = GarcomDao.selectOne(mesa.getIdgarcom());
					mesa.setGarcom(garcom);
				}
				stmt.close();
				con.close();
			}
			return mesa;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

}
